const XLSX = require("xlsx"); //无法设置筛选参数
const { getCSVData } = require("./readCsv.js");
const fs = require("fs");

function createFileName(fileName) {
  let name = fileName,
    count = 1;
  const lastIndex = fileName.lastIndexOf(".");
  const baseName = lastIndex != -1 ? fileName.slice(0, lastIndex) : fileName;
  const suffix = lastIndex != -1 ? fileName.slice(lastIndex) : "";
  while (fs.existsSync(name)) {
    name = `${baseName}(${count})${suffix}`;
    count++;
  }
  return name;
}

function complierExcel(dir = "") {
  // 读取 Excel 文件
  const workbook = XLSX.readFile(dir);

  // 获取第一个工作表（Sheet）
  const worksheet = workbook.Sheets[workbook.SheetNames[0]]; //workbook.SheetNames[0]是第一个sheet的名字

  // 将工作表转换为 JSON 对象数组
  const data = XLSX.utils.sheet_to_json(worksheet);

  // 输出 JavaScript 对象数组
  const map = data[0];
  let newArr = [];
  for (let i = 1; i < data.length; i++) {
    const item = data[i];
    let obj = {};
    Object.keys(item).forEach((key) => {
      if (map[key]) {
        let label = map[key];
        if (label.includes("支付订单号")) {
          label = "支付订单号";
        }
        obj[label] = item[key];
      }
    });
    newArr.push(obj);
  }
  // console.log(newArr.slice(0, 10));
  return newArr;
}
function getColumnIndex(worksheet, columnName) {
  const range = XLSX.utils.decode_range(worksheet["!ref"]);
  for (let c = 0; c <= range.e.c; c++) {
    const cell = worksheet[XLSX.utils.encode_cell({ r: 0, c })];
    if (cell && cell.v === columnName) {
      return c;
    }
  }
  return -1;
}
// 判断日期是否为月末最后一天
function isLastDayOfMonth(dateStr) {
  if (!dateStr) return false;

  // 尝试解析日期字符串
  const date = new Date(dateStr);
  if (isNaN(date.getTime())) {
    // 如果标准格式解析失败，尝试处理常见的中文日期格式
    // 例如：2025-4-30 或 2025.4.30 或 2025年4月30日
    const patterns = [
      /^(\d{4})[-./年](\d{1,2})[-./月](\d{1,2})/,
      /^(\d{1,2})[-./月](\d{1,2})[-./日],(\d{4})/,
    ];

    let matched = false;
    for (const pattern of patterns) {
      const match = dateStr.match(pattern);
      if (match) {
        const [_, year, month, day] = match;
        const parsedDate = new Date(
          parseInt(year),
          parseInt(month) - 1,
          parseInt(day)
        );
        if (!isNaN(parsedDate.getTime())) {
          // 检查是否为月末
          const lastDay = new Date(
            parsedDate.getFullYear(),
            parsedDate.getMonth() + 1,
            0
          ).getDate();
          return parseInt(day) === lastDay;
        }
        matched = true;
        break;
      }
    }

    if (!matched) return false;
  } else {
    // 标准日期格式解析成功
    // 获取下个月的第0天（即当前月的最后一天）
    // const lastDay = new Date(
    //   date.getFullYear(),
    //   date.getMonth() + 1,
    //   0
    // ).getDate();
    // return date.getDate() === lastDay;
    const nextDay = new Date(date);
    nextDay.setDate(date.getDate() + 1);
    return date.getMonth() !== nextDay.getMonth();
  }

  return false;
}

// 修改exportExcel函数
function exportExcel(dataSheets, exportFileName) {
  // 创建一个工作簿
  const workbook = XLSX.utils.book_new();

  // 如果传入的是单个数据集而非对象，则按照原来的方式处理
  if (!dataSheets.sheets) {
    const worksheet = XLSX.utils.json_to_sheet(dataSheets);
    XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");
  } else {
    // 处理多个工作表
    dataSheets.sheets.forEach((sheet) => {
      const worksheet = XLSX.utils.json_to_sheet(sheet.data);

      // 设置单元格样式（如果有标记的月末日期）
      if (sheet.data.some((item) => item._isMonthEnd)) {
        // 如果没有样式表，创建一个
        if (!worksheet["!styles"]) worksheet["!styles"] = {};

        // 遍历数据找出需要标记的行
        sheet.data.forEach((row, rowIndex) => {
          if (row._isMonthEnd) {
            // 获取日期列的索引
            const dateColIndex = Object.keys(row).findIndex(
              (key) =>
                key === "订单确认时间" ||
                key === "交易时间" ||
                key.includes("时间")
            );

            if (dateColIndex !== -1) {
              // 行索引需要+1因为表头占据第一行
              const cellRef = XLSX.utils.encode_cell({
                r: rowIndex + 1,
                c: dateColIndex,
              });

              // 设置单元格样式 - 黄色背景
              worksheet[cellRef].s = {
                fill: {
                  patternType: "solid",
                  fgColor: { rgb: "FFFF00" }, // 黄色
                },
              };
            }
          }
        });
      }

      // 设置列宽度
      if (sheet.options?.columnWidth) {
        worksheet["!cols"] = [];
        // 获取最大列数
        const range = XLSX.utils.decode_range(worksheet["!ref"]);
        const maxCol = range.e.c + 1;

        // 初始化所有列的默认宽度
        for (let i = 0; i < maxCol; i++) {
          worksheet["!cols"][i] = { width: 9 }; // 默认宽度
        }

        // 设置指定列的宽度
        Object.entries(sheet.options.columnWidth).forEach(([col, width]) => {
          // 将列字母转换为索引，例如 'A' -> 0, 'B' -> 1
          const colIndex = col.charCodeAt(0) - "A".charCodeAt(0);
          if (colIndex >= 0 && colIndex < maxCol) {
            worksheet["!cols"][colIndex] = { width };
          }
        });
      }

      if (sheet.options?.autoFilter && sheet.data.length > 0) {
        const range = XLSX.utils.decode_range(worksheet["!ref"]);

        // 获取列字母（假设"计数"是最后一列）
        // const filterCol = getColumnIndex(worksheet, "计数"); //range.e.c; // 取最后一列的索引
        const filterCol = sheet.options.autoFilter?.column
          ? getColumnIndex(worksheet, sheet.options.autoFilter.column)
          : -1;

        // 设置筛选条件和范围
        worksheet["!autofilter"] = {
          ref: XLSX.utils.encode_range({
            s: { r: 0, c: 0 },
            e: { r: range.e.r, c: range.e.c },
          }),
        };
        if (filterCol != -1) {
          const colLetter = XLSX.utils.encode_col(filterCol);
          worksheet["!autofilter"]["filterCriteria"] = {
            // 添加筛选条件（Excel过滤语法）
            [colLetter]: {
              filterType: "number",
              filters: [{ value: 1 }], // 筛选数值等于1
              filterOperator: "AND",
            },
          };
        }
      }

      XLSX.utils.book_append_sheet(workbook, worksheet, sheet.name);
    });
  }
  const excelName = createFileName(exportFileName);
  // 写入文件
  XLSX.writeFile(workbook, excelName);

  console.log("Excel 文件导出成功！", excelName);
}
function getExportFileName(files, name) {
  if (name) return name;
  if (files.length === 0) return "数据汇总.xlsx";

  // 从第一个文件名提取前缀和日期范围（示例：NW店铺订单全部4.1-4.30.xlsx）
  const fileName = files[0].split("/").pop(); // 获取纯文件名
  const pattern = /^([A-Za-z]{2,}).*?(\d+\.\d+-\d+\.\d+)/;
  const match = fileName.match(pattern);

  if (!match) return "数据汇总.xlsx";

  const [_, prefix, dateRange] = match;
  return `${prefix}${dateRange}汇总.xlsx`;
}
async function main(
  execlArrFile,
  payCsvArrFile,
  airCsvArrFile,
  exportFileName
) {
  // 读取 Excel 文件
  const newData = [];
  execlArrFile.forEach((item) => {
    const data = complierExcel(item);
    newData.push(...data);
  });
  // const data1 = complierExcel(excelFile1);
  // const data2 = complierExcel(excelFile2);
  // const newData = data1.concat(data2);

  const header = [
    "支付订单号",
    "货币单位",
    "订单总价",
    "商品总价",
    "优惠金额",
    "物流费用",
    "支付状态",
    "支付方式1",
    "支付渠道1",
    "支付金额1",
    "最后一次互动来源",
    "订单确认时间",
  ];
  const needNum = ["订单总价", "商品总价", "优惠金额", "物流费用"];
  // 创建通用的数据处理函数
  const processData = (data, condition) => {
    return data
      .filter(
        (item) =>
          ["部分退款", "已支付"].includes(item["支付状态"]) && condition(item)
      )
      .map((item) => {
        let obj = {};
        header.forEach((key) => {
          obj[key] = item[key];
          if (needNum.includes(key)) {
            obj[key] = parseFloat(obj[key]);
          }
        });
        return obj;
      });
  };
  // 筛选条件1：部分退款或已支付，且支付渠道不是空中云汇
  const payData = processData(
    newData,
    (item) => item["支付渠道1"] !== "空中云汇"
  );
  // 筛选条件2：对应云汇
  const airData = processData(
    newData,
    (item) => item["支付渠道1"] === "空中云汇"
  );

  const header1 = [
    "交易号",
    "币种",
    "总额",
    "费用",
    "净额",
    "类型",
    "状态",
    "来源",
  ];
  const checkout_types = [
    "快速结账付款",
    "直接信用卡付款",
    "预先批准付款账单用户付款",
  ];
  const header2 = [
    "交易编号",
    "交易币种",
    "交易金额",
    "授权状态返回描述",
    "交易状态",
  ];
  const checkout_types2 = [
    "FULLY REFUNDED",
    "PARTIALLY REFUNDED",
    "SUCCEEDED",
    "DISPUTED",
  ]; //"DISPUTED"云汇临时加上,这样才能对接上

  // TOOD 是否可以将csv文件不用区分, 直接判断分类
  // const csvData = await getCSVData(payCsvFile);
  const csvData = (await Promise.all(payCsvArrFile.map(getCSVData))).flat(1);
  // const csvData2 = await getCSVData(airCsvFile);
  const csvData2 = (await Promise.all(airCsvArrFile.map(getCSVData))).flat(1);

  // 交易号, 状态: 已完成; 以及满足相关的"类型"
  const payData2 = [];
  payData.forEach((item) => {
    let isRepeat = false;
    for (let i = 0; i < csvData.length; i++) {
      const csvItem = csvData[i];
      const isSame = csvItem["交易号"] === item["支付订单号"];
      if (isSame) {
        if (
          csvItem["状态"] === "已完成" &&
          checkout_types.includes(csvItem["类型"])
        ) {
          // 检查订单确定时间是否为月末
          const isMonthEnd = isLastDayOfMonth(item["订单确认时间"]);

          let newObj = {};
          for (let j = 0; j < header1.length; j++) {
            const key = header1[j];
            newObj[key] = csvItem[key];
            const isNum = header1.slice(2, 5).includes(key);
            if (isNum) {
              newObj[key] = parseFloat(newObj[key]);
            }
          }
          newObj["计数"] = 1;
          if (isRepeat) {
            newObj["计数"] = (newObj["计数"] || 0) + 1;
          }

          // 如果是月末，添加标记
          if (isMonthEnd) {
            newObj._isMonthEnd = true;
            item._isMonthEnd = true;
          }

          isRepeat = true;
          payData2.push(newObj);
        }
      }
    }
  });
  // 交易编号, 以及对应的 "交易状态"
  const airData2 = [];
  airData.forEach((item) => {
    let isRepeat = false;
    for (let i = 0; i < csvData2.length; i++) {
      const csvItem = csvData2[i];
      const isSame = csvItem["交易编号"] === item["支付订单号"];
      if (isSame) {
        if (
          // csvItem["状态"] === "已完成" &&
          checkout_types2.includes(csvItem["交易状态"])
        ) {
          let newObj = {};
          for (let j = 0; j < header2.length; j++) {
            const key = header2[j];
            newObj[key] = csvItem[key];
            const isNum = header2.slice(2, 3).includes(key);
            if (isNum) {
              newObj[key] = parseFloat(newObj[key]);
            }
          }
          newObj["计数"] = 1;
          if (isRepeat) {
            // console.log("重复", csvItem["交易号"]);
            newObj["计数"] = (newObj["计数"] || 0) + 1;
          }
          isRepeat = true;
          airData2.push(newObj);
        }
      }
    }
  });

  const paySum = currencySum(payData, "货币单位", "订单总价");
  const paySum2 = currencySum(payData2, "币种", "总额");
  const airSum = currencySum(airData, "货币单位", "订单总价");
  const airSum2 = currencySum(airData2, "交易币种", "交易金额");
  // 重组sumData为二维数组格式
  const currencies = new Set([
    ...Object.keys(paySum),
    ...Object.keys(paySum2),
    ...Object.keys(airSum),
    ...Object.keys(airSum2),
  ]);
  // const sumRows = ["币种", "店铺筛paypal", "paypal", "店铺筛云汇", "云汇"]
  const sumData = [];
  currencies.forEach((item) => {
    let obj = {
      币种: item,
      店铺筛paypal: paySum[item] || "",
      paypal: paySum2[item] || "",
      店铺筛云汇: airSum[item] || "",
      云汇: airSum2[item] || "",
    };
    sumData.push(obj);
  });
  sumData.sort((a, b) => a["币种"].localeCompare(b["币种"]));
  // 创建多工作表的Excel文件
  // 修改多工作表配置
  const multiSheetData = {
    sheets: [
      {
        name: "原始数据",
        data: newData,
        options: {
          autoFilter: true,
        },
      },
      {
        name: "店铺筛Paypal",
        data: payData,
        options: {
          autoFilter: true,
          columnWidth: {
            A: 20,
          },
        },
      },
      {
        name: "csv-PayPal",
        data: payData2,
        options: {
          columnWidth: {
            A: 20,
          },
          autoFilter: true,
        },
      },
      {
        name: "店铺筛云汇",
        data: airData,
        options: {
          autoFilter: true,
          columnWidth: {
            A: 25,
          },
        },
      },
      {
        name: "csv-云汇订单",
        data: airData2,
        options: {
          columnWidth: {
            A: 25,
          },
          autoFilter: true,
        },
      },
      {
        name: "数据汇总",
        data: sumData,
        options: {
          columnWidth: {
            A: 10,
            B: 15,
            C: 15,
            D: 15,
            E: 15,
          },
        },
      },
    ],
  };
  const efileName = getExportFileName(execlArrFile, exportFileName);
  // 导出多工作表Excel文件
  exportExcel(multiSheetData, efileName); // "NW2025.1.1-3.31_all.xlsx"
}

// 保留原来的单工作表导出，以保持兼容性
// exportExcel(needData, "NW2025.1.1-3.31_needData.xlsx");
// TODO 根据汇率换算成usd
function currencySum(data, curField = "货币单位", sumFiled = "订单总价") {
  let currcyObj = {};
  for (let i = 0; i < data.length; i++) {
    const item = data[i];
    if (item["计数"] && item["计数"] != 1) continue;
    let cur = currcyObj[item[curField]];
    currcyObj[item[curField]] = (cur || 0) + item[sumFiled];
  }
  return currcyObj;
  // console.table(currcyObj);
  // console.log("------------------------------------");
}

// 调用函数
main(
  ["../excel/3月/全部1.1-3.31.xlsx", "../excel/3月/已存档1.1-3.31.xlsx"],
  [
    "../excel/3月/521.CSV",
    "../excel/4/华宁PayPal4.1-4.30.CSV",
    "../excel/4/长沙PayPal4.1-4.30.CSV",
  ],
  ["../excel/3月/air2025.1.1-3.31.csv", "../excel/4/云汇尼彩4.1-4.31.csv"]
);
// main(
//   [
//     `../excel/5月/2025.5全部订单.xlsx`,
//     `../excel/5月/订单2025.5月份已存档.xlsx`,
//   ],
//   [`../excel/5月/长沙NW2025年5月份PayPal.CSV`,`../excel/5月/华宁2025年5月份NW数据.CSV`],
//   [`../excel/5月/尼彩云汇2025年5月份数据.csv`]
// );
